﻿using System;
using System.Collections.Generic;
using System.Linq;
using JStore.Core.Dtos;
using JStore.Core.Entities;
using SpeadFrm.MvcPager;
using SpeadFrm.ORM;

namespace JStore.Data.Daos
{
    /// <summary>
    ///
    /// </summary>
    public class UserInfoDao : BaseDao<UserInfo>
    {
        public bool ExistsUser(string userName)
        {
            var sql = Sql.Builder
                .Select("COUNT(1)")
                .From("UserInfo")
                .Where("UserName = @0", userName);
            return FirstOrDefault<int>(sql) > 0;
        }

        public UserInfo GetByUserName(string userName)
        {
            var sql = Sql.Builder.Where("UserName = @0", userName);
            return FirstOrDefault(sql);
        }

        public int GetJdCount(string userName)
        {
            var sql = Sql.Builder
              .Select("COUNT(1)")
              .From("UserInfo")
              .Where("JdUserName = @0", userName);
            return FirstOrDefault<int>(sql);
        }

        public List<UserInfo> GetParentJdList(string userName, int layer)
        {
            var sql = Sql.Builder
                 .Append("SELECT a.*")
                 .Append("FROM UserInfo a JOIN (SELECT * FROM dbo.GetParentJdList(@0,@1)) b ON a.UserId = b.JdUserId", userName, layer)
                 .Append("ORDER BY b.LayerIndex ASC");
            return Query(sql).ToList();
        }

        public UserInfo GetParentJdArea(string userName)
        {
            var sql = Sql.Builder.Where("UserId IN (SELECT UserId FROM dbo.GetParentJdArea(@0))", userName);
            return FirstOrDefault(sql);
        }

        public List<UserInfo> GetParentTjList(string userName, int layer)
        {
            var sql = Sql.Builder
              .Append("SELECT a.*")
              .Append("FROM UserInfo a JOIN (SELECT * FROM dbo.GetParentTjList(@0,@1)) b ON a.UserId = b.TjUserId", userName, layer)
              .Append("ORDER BY b.LayerIndex ASC");
            return Query(sql).ToList();
        }

        public int CheckJdHas(int parentUserId, int userId)
        {
            var sql = Sql.Builder.Append("EXECUTE CheckJdHas @0,@1", parentUserId, userId);
            return FirstOrDefault<int>(sql);
        }

        public UserInfo GetByAreaUserId(int userId)
        {
            var sql = Sql.Builder.Where("AreaUserId = @0", userId);
            return FirstOrDefault(sql);
        }

        public List<UserInfo> GetParentJdListWithArea(string userName)
        {
            var sql = Sql.Builder.Append("SELECT * FROM dbo.GetParentJdListWithArea(@0)", userName);
            return Query(sql).ToList();
        }

        public int GetJdTreeCount(string userName, int layer)
        {
            var sql = Sql.Builder
                .Append("SELECT COUNT(1) FROM dbo.GetJdListByUserName(@0,@1)", userName, layer);
            return FirstOrDefault<int>(sql);
        }

        public List<UserInfo> GetJdTreeList(string userName, int layer)
        {
            var sql = Sql.Builder
                .Append("SELECT a.*")
                .Append("FROM UserInfo a JOIN (SELECT * FROM dbo.GetJdListByUserName(@0,@1)) b ON a.UserId = b.UserId", userName, layer)
                .Append("ORDER BY b.LayerIndex ASC");
            return Query(sql).ToList();
        }

        public int GetTjCount(string userName)
        {
            var sql = Sql.Builder
                .Select("COUNT(1)")
                .From("UserInfo")
                .Where("TjUserName = @0", userName);
            return FirstOrDefault<int>(sql);
        }

        public PagedList<UserInfoDto> GetPagedList(string userName, string jdUserName, string tjUserName, int? userTypeId, string areaUserName, bool? isArea, int pageIndex, int pageSize)
        {
            var sql = Sql.Builder
                .Append("SELECT a.*,b.CardName AS CardTypeName,c.UserTypeName")
                .Append("FROM UserInfo a JOIN CardType b ON a.CardType = b.Id ")
                .Append("JOIN UserType c ON a.UserTypeId = c.Id");
            if (!string.IsNullOrEmpty(userName))
                sql.Where("a.UserName = @0", userName);
            if (!string.IsNullOrEmpty(jdUserName))
                sql.Where("a.JdUserName = @0", jdUserName);
            if (!string.IsNullOrEmpty(tjUserName))
                sql.Where("a.TjUserName = @0", tjUserName);
            if (userTypeId != null)
                sql.Where("a.UserTypeId = @0", userTypeId);
            if (!string.IsNullOrEmpty(areaUserName))
                sql.Where("a.AreaUserName = @0", areaUserName);
            if (isArea != null)
                sql.Where("a.IsArea = @0", isArea);
            sql.OrderBy("a.CreateDate DESC");
            return PagedList<UserInfoDto>(pageIndex, pageSize, sql);
        }

        public List<UserInfo> GetTjTreeList(string userName, int layer)
        {
            var sql = Sql.Builder
               .Append("SELECT a.*")
               .Append("FROM UserInfo a JOIN (SELECT * FROM dbo.GetTjListByUserName(@0,@1)) b ON a.UserId = b.UserId", userName, layer)
               .Append("ORDER BY b.LayerIndex ASC");
            return Query(sql).ToList();
        }

        public void SetTjUser(int userId, string userName, List<int> userIds)
        {
            var sql = Sql.Builder
                .Append("UPDATE UserInfo")
                .Append("SET TjUserId = @0,TjUserName = @1", userId, userName)
                .Where("UserId IN (@0)", userIds);
            Execute(sql);
        }

        public UserInfo GetByUserId(int userId)
        {
            var sql = Sql.Builder.Where("UserId = @0", userId);
            return FirstOrDefault(sql);
        }
 
    }
}

